﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;
using System.Reflection;
using System.Data.Common;
using System.Configuration;
using System.Diagnostics;
using System.Reflection.Emit;

namespace Trade.Library.Data
{
    /// <summary>
    /// 数据库类型
    /// </summary>
    public enum DataBaseType
    {
        SqlServer,
        MySql
    }

    /// <summary>
    /// 
    /// </summary>
    public class DBHelperFactory
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionStringName"></param>
        /// <returns></returns>
        public static DBHelper Create(string connectionStringName = null)
        {
            return new DBHelper(connectionStringName);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public static DBHelper Create(IDbConnection connection)
        {
            return new DBHelper(connection);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public static DBHelper Create(string connectionString, string providerName)
        {
            return new DBHelper(connectionString, providerName);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="provider"></param>
        /// <returns></returns>
        public static DBHelper Create(string connectionString, DbProviderFactory provider)
        {
            return new DBHelper(connectionString, provider);
        }
    }

    /// <summary>
    /// 数据库访问类
    /// </summary>
    public class DBHelper : IDisposable
    {
        private string _connectionString = string.Empty;
        private string _providerName = string.Empty;
        private DbProviderFactory _factory;
        private IDbConnection _sharedConnection;
        //private IDbTransaction _transaction;
        //private int _sharedConnectionDepth;
        //private int _transactionDepth;
        //private bool _transactionCancelled;
        private string _lastSql;
        private object[] _lastArgs;
        private string _paramPrefix = "@";
        private DataBaseType _dbType = DataBaseType.SqlServer;
#if DEBUG
        private Stopwatch _timeWatch = new Stopwatch();
#endif
        #region 公共属性
        /// <summary>
        /// 数据库类型
        /// </summary>
        public DataBaseType DbType
        {
            get { return _dbType; }
        }
        #endregion

        #region 构造实体
        /// <summary>
        /// 构造一个<see cref="DBHelper"/>实体对象
        /// </summary>
        /// <param name="connectionStringName">连接字符串名称，可选参数。如果为空则默认为配置文件中第一个连接字符串。</param>
        /// <example>
        /// <code>
        /// DBHelper db=new DBHelper();
        /// DBHelper db=new DBHelper("DefaultConnection");
        /// </code>
        /// </example>
        public DBHelper(string connectionStringName = null)
        {
            var providerName = "System.Data.SqlClient";
            if (string.IsNullOrEmpty(connectionStringName))
                connectionStringName = ConfigurationManager.ConnectionStrings[0].Name;
            if (ConfigurationManager.ConnectionStrings[connectionStringName] != null)
            {
                if (!string.IsNullOrEmpty(ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName))
                    providerName = ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName;
            }
            else
            {
                throw new InvalidOperationException("未能找到指定名称的连接字符串！");
            }
            _connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
            _providerName = providerName;
            CommonConstruct();
        }
        /// <summary>
        /// 构造一个<see cref="DBHelper"/>实体对象
        /// </summary>
        /// <param name="connection">指定的<see cref="IDbConnection"/>对象</param>
        /// <example>
        /// <code>
        /// IDbConnection idb = new OracleConnection("Data Source=orcl;User ID=ynjyorcl;PassWord=1;");
        /// </code>
        /// </example>
        public DBHelper(IDbConnection connection)
        {
            _sharedConnection = connection;
            _connectionString = connection.ConnectionString;
            //_sharedConnectionDepth = 2;		// 防止关闭外部连接
            CommonConstruct();
        }
        /// <summary>
        /// 构造一个<see cref="DBHelper"/>实体对象
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="providerName">用于访问基础数据存储区的 ADO.NET 提供程序的名称。</param>
        /// <example>
        /// <code>
        /// DBHelper db=new DBHelper("Data Source=orcl;User ID=ynjyorcl;PassWord=1;","System.Data.OracleClient");
        /// </code>
        /// </example>
        public DBHelper(string connectionString, string providerName = "System.Data.SqlClient")
        {
            _connectionString = connectionString;
            _providerName = providerName;
            CommonConstruct();
        }
        /// <summary>
        /// 构造一个<see cref="DBHelper"/>实体对象
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="provider">表示一组方法，这些方法用于创建提供程序对数据源类的实现的实例</param>
        /// <example>
        /// <code>
        /// DBHelper db=new DBHelper("Data Source=orcl;User ID=ynjyorcl;PassWord=1;",DbProviderFactories.GetFactory("System.Data.OracleClient"));
        /// </code>
        /// </example>
        public DBHelper(string connectionString, DbProviderFactory provider)
        {
            _connectionString = connectionString;
            _factory = provider;
            CommonConstruct();
        }
        /// <summary>
        /// 
        /// </summary>
        private void CommonConstruct()
        {
            //_transactionDepth = 0;
            if (_providerName != null)
                _factory = DbProviderFactories.GetFactory(_providerName);
            string dbtype = (_factory == null ? _sharedConnection.GetType() : _factory.GetType()).Name;
            // Try using type name first (more reliable)
            //string dbtype =  _factory.GetType().Name;
            if (dbtype.Contains("MySql")) _dbType = DataBaseType.MySql;
            else if (dbtype.Contains("System.Data.SqlClient")) _dbType = DataBaseType.SqlServer;
            else if (_providerName.IndexOf("MySql", StringComparison.InvariantCultureIgnoreCase) >= 0) _dbType = DataBaseType.MySql;
            if (_dbType == DataBaseType.MySql && _connectionString != null && _connectionString.IndexOf("Allow User Variables=true") >= 0)
                _paramPrefix = "?";

            //_sharedConnection = _factory.CreateConnection();
            //_sharedConnection.ConnectionString = _connectionString;
            //_sharedConnection.Open();
            //_sharedConnection.Dispose();
        }
        #endregion

        #region 管理连接
        /// <summary>
        /// 关闭打开的连接释放资源
        /// </summary>
        public void Dispose()
        {
            //当KeepConnectionAlive=true或者手动的打开一个共享的连接时
            //CloseSharedConnection();
            //_sharedConnection.Dispose();
            //_sharedConnection = null;
        }
        ///// <summary>
        ///// 连接保持状态
        ///// <para><c>true</c>:保持连接状态处于激活状态直到连接被回收</para>
        ///// <para><c>false</c>:不保持连接处于激活状态</para>
        ///// </summary>
        //public bool KeepConnectionAlive { get; set; }
        ///// <summary>
        ///// 打开一个连接（可以嵌套打开多个）
        ///// </summary>
        //public void OpenSharedConnection()
        //{
        //    if (_sharedConnectionDepth == 0)
        //    {
        //        _sharedConnection = _factory.CreateConnection();
        //        _sharedConnection.ConnectionString = _connectionString;
        //        _sharedConnection.Open();

        //        _sharedConnection = OnConnectionOpened(_sharedConnection);

        //        if (KeepConnectionAlive)
        //            _sharedConnectionDepth++;		// Make sure you call Dispose
        //    }
        //    if (_sharedConnection.State != ConnectionState.Open)
        //    {
        //        _sharedConnection.Open();
        //    }
        //    _sharedConnectionDepth++;
        //}

        IDbConnection DbService()
        {
            if (_factory != null && !string.IsNullOrEmpty(_connectionString))
                return ConnectionPool.GetConnection(_connectionString, _factory);
            else
            {
                _sharedConnection.ConnectionString = _connectionString;
                _sharedConnection.Open();
                return _sharedConnection;
            }
        }

        ///// <summary>
        ///// 关闭一个以前的连接
        ///// </summary>
        //public void CloseSharedConnection()
        //{
        //    if (_sharedConnectionDepth > 0)
        //    {
        //        _sharedConnectionDepth--;
        //        if (_sharedConnectionDepth == 0)
        //        {
        //            OnConnectionClosing(_sharedConnection);
        //            _sharedConnection.Dispose();
        //            _sharedConnection = null;
        //        }
        //    }
        //}
        /// <summary>
        /// 获取当前的连接对象
        /// </summary>
        public IDbConnection Connection
        {
            get { return DbService(); }
        }
        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public Transaction BeginTransaction()
        {
            return new Transaction(Connection);
            //if (_sharedConnection == null || _sharedConnection.ConnectionString == null)
            //    _sharedConnection = DbService();
            //_transaction = _sharedConnection.BeginTransaction();
            //return _transaction;
        }


        #region 可重写用于输出信息的方法
        /// <summary>
        /// 委托
        /// </summary>
        public delegate void deleOnBeginTransaction();
        /// <summary>
        /// 事务开始执行时执行的事件
        /// </summary>
        /// <example>
        /// <code>
        /// db.OnBeginTracnsactionEvent += () => System.Diagnostics.Debug.WriteLine("事务开始执行了......");
        /// </code>
        /// </example>
        public event deleOnBeginTransaction OnBeginTracnsactionEvent;
        /// <summary>
        /// 当事务开启时
        /// </summary>
        private void OnBeginTransaction()
        {
            if (OnBeginTracnsactionEvent != null)
                OnBeginTracnsactionEvent();
        }
        /// <summary>
        /// 委托
        /// </summary>
        public delegate void deleOnEndTransaction();
        /// <summary>
        /// 事务结束执行时执行的事件
        /// </summary>
        /// <example>
        /// <code>
        /// db.OnEndTransactionEvent += new DBHelper.deleOnBeginTransaction(TestBegin);
        /// void TestBegin()
        /// {
        ///     System.Diagnostics.Debug.WriteLine("事务执行结束了......");
        /// }
        /// </code>
        /// </example>
        public event deleOnEndTransaction OnEndTransactionEvent;
        /// <summary>
        /// 当事务结束时
        /// </summary>
        private void OnEndTransaction()
        {
            if (OnEndTransactionEvent != null)
                OnEndTransactionEvent();
        }
        /// <summary>
        /// 委托
        /// </summary>
        public delegate void deleOnException(Exception x);
        /// <summary>
        /// 出现异常时执行的事件
        /// </summary>
        /// <remarks>
        /// 不管是否定义异常时执行的事件，如果在调试时出现异常，都会使用System.Diagnostics.Debug.WriteLine()将异常信息及最后执行的Command命令输出。
        /// </remarks>
        /// <example>
        /// <code>
        /// db.OnExceptionEvent += delegate() { System.Diagnostics.Debug.WriteLine("出现异常了......"); };
        /// </code>
        /// </example>
        public event deleOnException OnExceptionEvent;
        /// <summary>
        /// 出现异常时
        /// </summary>
        /// <param name="x"></param>
        private void OnException(Exception x)
        {
            if (OnExceptionEvent != null)
                OnExceptionEvent(x);
            System.Diagnostics.Debug.WriteLine(x.ToString());
            System.Diagnostics.Debug.WriteLine(LastCommand);
        }
        /// <summary>
        /// 委托
        /// </summary>
        public delegate void deleOnConnectionOpened(IDbConnection conn);
        /// <summary>
        /// 当连接打开时执行的事件
        /// </summary>
        /// <example>
        /// <code>
        /// db.OnConnectionOpenedEvent += delegate() { System.Diagnostics.Debug.WriteLine("连接打开了......"); };
        /// </code>
        /// </example>
        public event deleOnConnectionOpened OnConnectionOpenedEvent;
        /// <summary>
        /// 当连接打开时
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        private IDbConnection OnConnectionOpened(IDbConnection conn)
        {
            if (OnConnectionOpenedEvent != null)
                OnConnectionOpenedEvent(conn);
            return conn;
        }
        /// <summary>
        /// 委托
        /// </summary>
        public delegate void deleOnConnectionClosing(IDbConnection conn);
        /// <summary>
        /// 当连接关闭时执行的事件
        /// </summary>
        /// <example>
        /// <code>
        /// db.OnConnectionClosingEvent += () => System.Diagnostics.Debug.WriteLine("连接关闭了......");
        /// </code>
        /// </example>
        public event deleOnConnectionClosing OnConnectionClosingEvent;
        /// <summary>
        /// 当连接关闭时
        /// </summary>
        /// <param name="conn"></param>
        private void OnConnectionClosing(IDbConnection conn)
        {
            if (OnConnectionClosingEvent != null)
                OnConnectionClosingEvent(conn);
        }
        /// <summary>
        /// 委托
        /// </summary>
        public delegate void deleOnExecutingCommand(IDbCommand cmd);
        /// <summary>
        /// 执行Comman命令前执行的事件
        /// </summary>
        /// <example>
        /// <code>
        /// db.OnExecutingCommandEvent += () => System.Diagnostics.Debug.WriteLine("命令开始执行了......");
        /// </code>
        /// </example>
        public event deleOnExecutingCommand OnExecutingCommandEvent;
        /// <summary>
        /// 当执行command命令时
        /// </summary>
        /// <param name="cmd"></param>
        private void OnExecutingCommand(IDbCommand cmd)
        {
            if (OnExecutingCommandEvent != null)
                OnExecutingCommandEvent(cmd);
#if DEBUG
            _timeWatch.Reset();
            _timeWatch.Start();
#endif
        }
        /// <summary>
        /// 委托
        /// </summary>
        public delegate void deleOnExecutedCommand(IDbCommand cmd);
        /// <summary>
        /// 执行Comman命令后执行的事件
        /// </summary>
        /// <remarks>
        /// 无论是否定义了事件，程序都会将最后一次执行的command命令输出。
        /// </remarks>
        /// <example>
        /// <code>
        /// db.OnExecutedCommandEvent += () => System.Diagnostics.Debug.WriteLine("命令执行完了......");
        /// </code>
        /// </example>
        public event deleOnExecutedCommand OnExecutedCommandEvent;
        /// <summary>
        /// 当command命令执行完
        /// </summary>
        /// <param name="cmd"></param>
        private void OnExecutedCommand(IDbCommand cmd)
        {
            if (OnExecutedCommandEvent != null)
                OnExecutedCommandEvent(cmd);
#if DEBUG
            _timeWatch.Stop();
#endif
            System.Diagnostics.Debug.WriteLine(LastCommand);
        }

        static StringBuilder sb = new StringBuilder();
        #endregion

        #region 准备Command
        //正则，\w匹配字母或数字或下划线或汉字
        static Regex rxParams = new Regex(@"(?<!@)@\w+", RegexOptions.Compiled);
        /// <summary>
        /// 处理sql语句中的参数
        /// </summary>
        /// <param name="_sql"></param>
        /// <param name="args_src"></param>
        /// <param name="args_dest"></param>
        /// <returns></returns>
        private string ProcessParams(string _sql, object[] args_src, List<object> args_dest)
        {
            return rxParams.Replace(_sql, m =>
            {
                string param = m.Value.Substring(1);
                object arg_val;
                int paramIndex;
                if (int.TryParse(param, out paramIndex))
                {
                    //参数如果是数字
                    if (paramIndex < 0 || paramIndex >= args_src.Length)
                        throw new ArgumentOutOfRangeException(string.Format("Parameter '@{0}' specified but only {1} parameters supplied (in `{2}`)", paramIndex, args_src.Length, _sql));
                    arg_val = args_src[paramIndex];
                }
                else
                {
                    // 参数如果不是数字则在args_src中查找名称相同的项
                    bool found = false;
                    arg_val = null;
                    foreach (var o in args_src)
                    {
                        var pi = o.GetType().GetProperty(param);
                        if (pi != null)
                        {
                            arg_val = pi.GetValue(o, null);
                            found = true;
                            break;
                        }
                    }

                    if (!found)
                        throw new ArgumentException(string.Format("Parameter '@{0}' specified but none of the passed arguments have a property with this name (in '{1}')", param, _sql));
                }

                // 将参数全部转换成数字参数
                if ((arg_val as System.Collections.IEnumerable) != null &&
                    (arg_val as string) == null &&
                    (arg_val as byte[]) == null)
                {
                    var sb = new StringBuilder();
                    foreach (var i in arg_val as System.Collections.IEnumerable)
                    {
                        sb.Append((sb.Length == 0 ? "@" : ",@") + args_dest.Count.ToString());
                        args_dest.Add(i);
                    }
                    return sb.ToString();
                }
                else
                {
                    args_dest.Add(arg_val);
                    return "@" + (args_dest.Count - 1).ToString();
                }
            }
            );
        }

        /// <summary>
        /// 给DBCommand加载参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="item"></param>
        /// <param name="ParameterPrefix"></param>
        void AddParam(IDbCommand cmd, object item, string ParameterPrefix)
        {
            bool isStoredProcedure = false;
            if (cmd.CommandType == CommandType.StoredProcedure)
            {
                isStoredProcedure = true;
            }
            var idbParam = item as IDbDataParameter;
            if (idbParam != null)
            {
                if (!isStoredProcedure)
                    idbParam.ParameterName = string.Format("{0}{1}", ParameterPrefix, cmd.Parameters.Count);
                cmd.Parameters.Add(idbParam);
                return;
            }

            if (!isStoredProcedure)
            {
                var p = cmd.CreateParameter();
                p.ParameterName = string.Format("{0}{1}", ParameterPrefix, cmd.Parameters.Count);
                if (item == null)
                {
                    p.Value = DBNull.Value;
                }
                else
                {
                    var t = item.GetType();
                    if (t.IsEnum)		// PostgreSQL .NET driver wont cast enum to int
                    {
                        p.Value = (int)item;
                    }
                    else if (t == typeof(Guid))
                    {
                        p.Value = item.ToString();
                        p.DbType = System.Data.DbType.String;
                        p.Size = 40;
                    }
                    else if (t == typeof(string))
                    {
                        p.Size = Math.Max((item as string).Length + 1, 4000);		// Help query plan caching by using common size
                        p.Value = item;
                    }
                    else if (t == typeof(AnsiString))
                    {
                        // Thanks @DataChomp for pointing out the SQL Server indexing performance hit of using wrong string type on varchar
                        p.Size = Math.Max((item as AnsiString).Value.Length + 1, 4000);
                        p.Value = (item as AnsiString).Value;
                        p.DbType = System.Data.DbType.AnsiString;
                    }
                    else if (item.GetType().Name == "SqlGeography") //SqlGeography is a CLR Type
                    {
                        p.GetType().GetProperty("UdtTypeName").SetValue(p, "geography", null); //geography is the equivalent SQL Server Type
                        p.Value = item;
                    }

                    else if (item.GetType().Name == "SqlGeometry") //SqlGeometry is a CLR Type
                    {
                        p.GetType().GetProperty("UdtTypeName").SetValue(p, "geometry", null); //geography is the equivalent SQL Server Type
                        p.Value = item;
                    }
                    else
                    {
                        p.Value = item;
                    }
                }
                cmd.Parameters.Add(p);
            }
            else
            {
                PropertyInfo[] pis = item.GetType().GetProperties();
                foreach (PropertyInfo pi in pis)
                {
                    var p = cmd.CreateParameter();
                    p.ParameterName = string.Format("{0}{1}", ParameterPrefix, pi.Name);
                    var value = pi.GetValue(item, null);
                    if (value == null)
                        p.Value = DBNull.Value;
                    else
                    {
                        if (pi.PropertyType.IsEnum)		// PostgreSQL .NET driver wont cast enum to int
                        {
                            p.Value = (int)value;
                        }
                        else if (pi.PropertyType == typeof(Guid))
                        {
                            p.Value = value.ToString();
                            p.DbType = System.Data.DbType.String;
                            p.Size = 40;
                        }
                        else if (pi.PropertyType == typeof(string))
                        {
                            p.Size = Math.Max((value as string).Length + 1, 4000);
                            p.Value = value;
                        }
                        else if (pi.PropertyType == typeof(AnsiString))
                        {
                            p.Size = Math.Max((value as AnsiString).Value.Length + 1, 4000);
                            p.Value = (value as AnsiString).Value;
                            p.DbType = System.Data.DbType.AnsiString;
                        }
                        else
                        {
                            p.Value = value;
                        }
                    }
                    cmd.Parameters.Add(p);
                }
            }
        }

        // Create a command
        static Regex rxParamsPrefix = new Regex(@"(?<!@)@\w+", RegexOptions.Compiled);
        /// <summary>
        /// 创建一个Comman命令
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="commType"><see cref="CommandType"/></param>
        /// <param name="sql">SQL语句</param>
        /// <param name="args">参数</param>
        /// <returns>返回一个<see cref="IDbCommand"/>对象</returns>
        public IDbCommand CreateCommand(IDbTransaction tran, IDbConnection connection, CommandType commType, string sql, params object[] args)
        {
            bool isStoredProcedure = false;
            if (commType == CommandType.StoredProcedure)
                isStoredProcedure = true;
            if (!isStoredProcedure)
            {
                var new_args = new List<object>();
                sql = ProcessParams(sql, args, new_args);
                args = new_args.ToArray();
            }

            // 参数前缀是@还是:
            if (_paramPrefix != "@")
                sql = rxParamsPrefix.Replace(sql, m => _paramPrefix + m.Value.Substring(1));
            sql = sql.Replace("@@", "@");		   // <- double @@ escapes a single @

            // 创建command并且加载参数
            IDbCommand cmd = connection.CreateCommand();
            cmd.Connection = connection;
            cmd.CommandText = sql;
            if (tran != null && tran.Connection != null)
            {
                cmd.Transaction = tran;
                cmd.Connection = tran.Connection;
            }
            cmd.CommandType = commType;
            foreach (var item in args)
            {
                AddParam(cmd, item, _paramPrefix);
            }

            if (!String.IsNullOrEmpty(sql))
                DoPreExecute(cmd);

            return cmd;
        }

        /// <summary>
        /// 获取或设置在终止执行命令的尝试并生成错误之前的等待时间
        /// </summary>
        public int CommandTimeout { get; set; }

        /// <summary>
        /// 获取或设置在终止执行命令的尝试并生成错误之前的等待时间.
        /// <para>与<see cref="CommandTimeout"/>不同的是该时间设置后只有效一次。</para>
        /// </summary>
        public int OneTimeCommandTimeout { get; set; }

        void DoPreExecute(IDbCommand cmd)
        {
            // Setup command timeout
            if (OneTimeCommandTimeout != 0)
            {
                cmd.CommandTimeout = OneTimeCommandTimeout;
                OneTimeCommandTimeout = 0;
            }
            else if (CommandTimeout != 0)
            {
                cmd.CommandTimeout = CommandTimeout;
            }
            else if (System.Configuration.ConfigurationManager.AppSettings["CommandTimeout"] != null)
            {
                int commTimeout = 0;
                if (int.TryParse(System.Configuration.ConfigurationManager.AppSettings["CommandTimeout"], out commTimeout))
                {
                    cmd.CommandTimeout = commTimeout;
                }
            }

            // Call hook
            OnExecutingCommand(cmd);

            // Save it
            _lastSql = cmd.CommandText;
            _lastArgs = (from IDataParameter parameter in cmd.Parameters select parameter.Value).ToArray();
        }

        /// <summary>
        /// 最后一次执行的SQL语句
        /// </summary>
        public string LastSQL { get { return _lastSql; } }

        /// <summary>
        /// 最后一次执行时的参数
        /// </summary>
        public object[] LastArgs { get { return _lastArgs; } }

        /// <summary>
        /// 最后执行的Command命令
        /// </summary>
        public string LastCommand
        {
            get { return FormatCommand(_lastSql, _lastArgs); }
        }

        /// <summary>
        /// 格式化Command命令
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public string FormatCommand(IDbCommand cmd)
        {
            return FormatCommand(cmd.CommandText, (from IDataParameter parameter in cmd.Parameters select parameter.Value).ToArray());
        }

        /// <summary>
        /// 格式化Command命令
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public string FormatCommand(string sql, object[] args)
        {
            var sb = new StringBuilder();
            if (sql == null)
                return "";

            sb.AppendFormat("{0}", sql);
            if (args != null && args.Length > 0)
            {
                sb.Append("\n");
                for (int i = 0; i < args.Length; i++)
                {
                    if (args[i] != null)
                        sb.AppendFormat("\t -> {0}{1} [{2}] = \"{3}\"\n", _paramPrefix, i, args[i].GetType().Name, args[i]);
                }
                sb.Remove(sb.Length - 1, 1);
            }
#if DEBUG
            sb.AppendFormat("\n总耗时：{0} ms", _timeWatch.ElapsedMilliseconds.ToString("N0"));
#endif
            return sb.ToString();
        }
        #endregion

        #region 执行Command命令
        /// <summary>
        /// 执行<see cref="ExecuteNonQuery"/>命令返回影响的行数。
        /// <para>默认<see cref="CommandType"/>为<c>CommandType.Text</c></para>
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="args">参数，可为空</param>
        /// <returns>返回影响的行数</returns>
        /// <example><code>
        /// //不使用参数
        /// db.ExecuteNonQuery(@"update tb_hospitalprocurecatalog set stock=stock+1 where orgid='Y292604' and procurecatalogid='6155'");
        /// //使用数组参数，这是SQL语句中需要使用@0、@1......@N去标识参数
        /// db.ExecuteNonQuery(@"update tb_hospitalprocurecatalog set stock=stock+1 where orgid=@0 and procurecatalogid=@1", new object[] { "Y292604", "6155" });
        /// //使用匿名对象或者实体类
        /// db.ExecuteNonQuery(@"update tb_hospitalprocurecatalog set stock=stock+1 where orgid=@orgid and procurecatalogid=@procurecatalogid", new { orgid = "Y292604", procurecatalogid = "6155" });
        /// db.ExecuteNonQuery(@"update tb_hospitalprocurecatalog set stock=stock+1 where orgid=@orgid and procurecatalogid=@procurecatalogid", new Trade.Library.WebTest.Model.TB_HOSPITALPROCURECATALOG() { ORGID = "Y292604", PRICE = 123, PROCURECATALOGID = "6155" });
        /// </code></example>
        public int ExecuteNonQuery(string sql, params object[] args)
        {
            CommandType commType = new CommandType();
            return ExecuteNonQuery(commType, sql, args);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tran"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(Transaction tran, string sql, params object[] args)
        {
            CommandType commType = new CommandType();
            return ExecuteNonQuery(tran, commType, sql, args);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tran"></param>
        /// <param name="commType"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(Transaction tran, CommandType commType, string sql, params object[] args)
        {
            try
            {
                using (var cmd = CreateCommand(tran.tran, tran.conn, commType, sql, args))
                {
                    var retv = cmd.ExecuteNonQuery();
                    OnExecutedCommand(cmd);
                    return retv;
                }

            }
            catch (Exception x)
            {
                OnException(x);
                throw;
            }
        }

        /// <summary>
        /// 执行<see cref="ExecuteNonQuery"/>命令返回影响的行数。
        /// </summary>
        /// <param name="commType"><see cref="CommandType"/></param>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="args">参数，可为空</param>
        /// <returns>返回影响的行数</returns>
        /// <example><code>
        /// IDataParameter[] paras3 = new IDataParameter[] { 
        ///         new OracleParameter("in_orgid", "Y292604"), 
        ///         new OracleParameter("in_procurecatalogid", "6155"), 
        ///         new OracleParameter("out_Success", OracleType.Int32) { Direction = ParameterDirection.Output }
        ///         };
        ///        db.ExecuteNonQuery(CommandType.StoredProcedure, "Test", paras3);
        ///        int success = (int)paras3[paras3.Length - 1].Value;
        /// </code></example>
        public int ExecuteNonQuery(CommandType commType, string sql, params object[] args)
        {
            try
            {
                using (var _Connection = DbService())
                {
                    using (var cmd = CreateCommand(null, _Connection, commType, sql, args))
                    {
                        var retv = cmd.ExecuteNonQuery();
                        OnExecutedCommand(cmd);
                        return retv;
                    }
                }
            }
            catch (Exception x)
            {
                OnException(x);
                throw;
            }
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
        /// <para>默认<see cref="CommandType"/>为<c>CommandType.Text</c></para>
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="args">参数，可为空</param>
        /// <returns>结果集中第一行的第一列</returns>
        /// <example><code>
        /// object obj=db.ExecuteScalar(@"select amount from tb_purchaseorderdetail where orderdetailid=@0", new object[] { "7157EC02-B6CB-48B6-943A-731BD568CB98" });
        /// </code></example>
        public object ExecuteScalar(string sql, params object[] args)
        {
            CommandType commType = new CommandType();
            return ExecuteScalar(commType, sql, args);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tran"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public object ExecuteScalar(Transaction tran, string sql, params object[] args)
        {
            CommandType commType = new CommandType();
            return ExecuteScalar(tran, commType, sql, args);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tran"></param>
        /// <param name="commType"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public object ExecuteScalar(Transaction tran, CommandType commType, string sql, params object[] args)
        {
            try
            {
                using (var cmd = CreateCommand(tran.tran, tran.conn, commType, sql, args))
                {
                    object val = cmd.ExecuteScalar();
                    OnExecutedCommand(cmd);
                    return val;
                }
            }
            catch (Exception x)
            {
                OnException(x);
                throw;
            }
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
        /// </summary>
        /// <param name="commType"><see cref="CommandType"/></param>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="args">参数，可为空</param>
        /// <returns>结果集中第一行的第一列</returns>
        public object ExecuteScalar(CommandType commType, string sql, params object[] args)
        {
            try
            {
                using (var _Connection = DbService())
                {
                    using (var cmd = CreateCommand(null, _Connection, commType, sql, args))
                    {
                        object val = cmd.ExecuteScalar();
                        OnExecutedCommand(cmd);
                        return val;
                    }
                }

            }
            catch (Exception x)
            {
                OnException(x);
                throw;
            }
        }

        /// <summary>
        /// 泛型方法，执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
        /// </summary>
        /// <typeparam name="T">返回值的类型</typeparam>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="args">参数，可为空</param>
        /// <returns>结果集中第一行的第一列</returns>
        /// <remarks>当泛型T是不是可空的值类型时，查询结果为空时返回值类型的默认值。</remarks>
        /// <example><code>
        /// decimal? dec1 = db.ExecuteScalar<decimal?>(@"select amount from tb_purchaseorderdetail where orderdetailid=@orderdetailid", new  { orderdetailid="7157EC02-B6CB-48B6-943A-731BD568CB98" });
        /// decimal dec2 = db.ExecuteScalar<decimal>(@"select amount from tb_purchaseorderdetail where orderdetailid=@orderdetailid", new  { orderdetailid="7157EC02-B6CB-48B6-943A-731BD568CB98" });
        /// </code></example>
        public T ExecuteScalar<T>(string sql, params object[] args)
        {
            CommandType commType = new CommandType();
            return ExecuteScalar<T>(commType, sql, args);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tran"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public T ExecuteScalar<T>(Transaction tran, string sql, params object[] args)
        {
            CommandType commType = new CommandType();
            return ExecuteScalar<T>(tran, commType, sql, args);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tran"></param>
        /// <param name="commType"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public T ExecuteScalar<T>(Transaction tran, CommandType commType, string sql, params object[] args)
        {
            try
            {
                using (var cmd = CreateCommand(tran.tran, tran.conn, commType, sql, args))
                {
                    object val = cmd.ExecuteScalar();
                    OnExecutedCommand(cmd);
                    return CommonMap.ChangeType<T>(val);
                }
            }
            catch (Exception x)
            {
                OnException(x);
                throw;
            }
        }

        /// <summary>
        /// 泛型方法，执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
        /// </summary>
        /// <typeparam name="T">返回值的类型</typeparam>
        /// <param name="commType"><see cref="CommandType"/></param>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="args">参数，可为空</param>
        /// <returns>结果集中第一行的第一列</returns>
        /// <example><code>
        /// DateTime? dec1 = db.ExecuteScalar<DateTime?>(@"select EXECUTETIME from tb_purchaseorderdetail where orderdetailid=@orderdetailid", new  { orderdetailid="7157EC02-B6CB-48B6-943A-731BD568CB98" });
        /// DateTime dec2 = db.ExecuteScalar<DateTime>(@"select EXECUTETIME from tb_purchaseorderdetail where orderdetailid=@orderdetailid", new  { orderdetailid="7157EC02-B6CB-48B6-943A-731BD568CB98" });
        /// </code></example>
        public T ExecuteScalar<T>(CommandType commType, string sql, params object[] args)
        {
            try
            {
                using (var _Connection = DbService())
                {
                    using (var cmd = CreateCommand(null, _Connection, commType, sql, args))
                    {
                        object val = cmd.ExecuteScalar();
                        OnExecutedCommand(cmd);
                        return CommonMap.ChangeType<T>(val);
                    }
                }
            }
            catch (Exception x)
            {
                OnException(x);
                throw;
            }
        }

        /// <summary>
        /// 执行SQL语句并返回一个<c>System.Data.IDataReader</c>结果集
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="args">参数，可为空</param>
        /// <returns>返回一个<c>System.Data.IDataReader</c>结果集</returns>
        public IDataReader ExecuteDataReader(string sql, params object[] args)
        {
            CommandType commType = new CommandType();
            return ExecuteDataReader(commType, sql, args);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tran"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public IDataReader ExecuteDataReader(Transaction tran, string sql, params object[] args)
        {
            CommandType commType = new CommandType();
            return ExecuteDataReader(tran, commType, sql, args);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tran"></param>
        /// <param name="commType"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public IDataReader ExecuteDataReader(Transaction tran, CommandType commType, string sql, params object[] args)
        {
            try
            {
                var cmd = CreateCommand(tran.tran, tran.conn, commType, sql, args);
                IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                OnExecutedCommand(cmd);
                return idr;
            }
            catch (Exception x)
            {
                OnException(x);
                throw;
            }
        }

        /// <summary>
        /// 执行SQL语句并返回一个<c>System.Data.IDataReader</c>结果集
        /// </summary>
        /// <param name="commType"><see cref="CommandType"/></param>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="args">参数，可为空</param>
        /// <returns>返回一个<c>System.Data.IDataReader</c>结果集</returns>
        public IDataReader ExecuteDataReader(CommandType commType, string sql, params object[] args)
        {
            try
            {
                var cmd = CreateCommand(null, Connection, commType, sql, args);
                IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                OnExecutedCommand(cmd);
                return idr;
            }
            catch (Exception x)
            {
                OnException(x);
                throw;
            }
        }

        /// <summary>
        /// 执行SQL语句并返回一个<c>System.Data.DataSet</c>结果集
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="args">参数，可为空</param>
        /// <returns>返回一个<c>System.Data.DataSet</c>结果集</returns>
        public DataSet ExecuteDataSet(string sql, params object[] args)
        {
            CommandType commType = new CommandType();
            return ExecuteDataSet(commType, sql, args);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tran"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(Transaction tran, string sql, params object[] args)
        {
            CommandType commType = new CommandType();
            return ExecuteDataSet(tran, commType, sql, args);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tran"></param>
        /// <param name="commType"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(Transaction tran, CommandType commType, string sql, params object[] args)
        {
            try
            {
                using (var cmd = CreateCommand(tran.tran, tran.conn, commType, sql, args))
                {
                    DbDataAdapter da = _factory.CreateDataAdapter();
                    da.SelectCommand = (DbCommand)cmd;
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    OnExecutedCommand(cmd);
                    return ds;
                }
            }
            catch (Exception x)
            {
                OnException(x);
                throw;
            }
        }

        /// <summary>
        /// 执行SQL语句并返回一个<c>System.Data.DataSet</c>结果集
        /// </summary>
        /// <param name="commType"><see cref="CommandType"/></param>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="args">参数，可为空</param>
        /// <returns>返回一个<c>System.Data.DataSet</c>结果集</returns>
        public DataSet ExecuteDataSet(CommandType commType, string sql, params object[] args)
        {
            try
            {
                using (var _Connection = DbService())
                {
                    using (var cmd = CreateCommand(null, _Connection, commType, sql, args))
                    {
                        DbDataAdapter da = _factory.CreateDataAdapter();
                        da.SelectCommand = (DbCommand)cmd;
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        OnExecutedCommand(cmd);
                        return ds;
                    }
                }
            }
            catch (Exception x)
            {
                OnException(x);
                throw;
            }
        }

        /// <summary>
        /// 执行SQL语句并返回一个<c>System.Data.DataTable</c>结果集
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="args">参数，可为空</param>
        /// <returns>返回一个<c>System.Data.DataTable</c>结果集</returns>
        public DataTable ExecuteDataTable(string sql, params object[] args)
        {
            CommandType commType = new CommandType();
            return ExecuteDataTable(commType, sql, args);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tran"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(Transaction tran, string sql, params object[] args)
        {
            CommandType commType = new CommandType();
            return ExecuteDataTable(tran, commType, sql, args);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tran"></param>
        /// <param name="commType"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(Transaction tran, CommandType commType, string sql, params object[] args)
        {
            try
            {
                using (var _Connection = DbService())
                {
                    using (var cmd = CreateCommand(tran.tran, tran.conn, commType, sql, args))
                    {
                        DbDataAdapter da = _factory.CreateDataAdapter();
                        da.SelectCommand = (DbCommand)cmd;
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        OnExecutedCommand(cmd);
                        return dt;
                    }
                }
            }
            catch (Exception x)
            {
                OnException(x);
                throw;
            }
        }

        /// <summary>
        /// 执行SQL语句并返回一个<c>System.Data.DataTable</c>结果集
        /// </summary>
        /// <param name="commType"><see cref="CommandType"/></param>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="args">参数，可为空</param>
        /// <returns>返回一个<c>System.Data.DataTable</c>结果集</returns>
        public DataTable ExecuteDataTable(CommandType commType, string sql, params object[] args)
        {
            try
            {
                using (var _Connection = DbService())
                {
                    using (var cmd = CreateCommand(null, _Connection, commType, sql, args))
                    {
                        DbDataAdapter da = _factory.CreateDataAdapter();
                        da.SelectCommand = (DbCommand)cmd;
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        OnExecutedCommand(cmd);
                        return dt;
                    }
                }
            }
            catch (Exception x)
            {
                OnException(x);
                throw;
            }
        }
        #endregion

        #region 查询
        /// <summary>
        /// 泛型方法，执行查询，返回<c>IEnumerable<T></c>结果集
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="args">参数</param>
        /// <returns>返回<c>IEnumerable<T></c>结果集</returns>
        /// <example><code>
        /// IEnumerable<Model.TB_HOSPITALPROCURECATALOG> ie1=db.Query<Model.TB_HOSPITALPROCURECATALOG>("select * from tb_hospitalprocurecatalog where orgid='Y292604' and rownum<=20");
        /// List<Model.TB_HOSPITALPROCURECATALOG> lst1 = db.Query<Model.TB_HOSPITALPROCURECATALOG>("select * from tb_hospitalprocurecatalog where orgid='Y292604' and rownum<=20").ToList();
        /// Dictionary<string,string> dic1=db.Query<Model.TB_HOSPITALPROCURECATALOG>("select * from tb_hospitalprocurecatalog where orgid='Y292604' and rownum<=20").ToDictionary<Model.TB_HOSPITALPROCURECATALOG, string, string>(p => p.ORGID, p => p.PROCURECATALOGID);
        /// </code></example>
        public IEnumerable<T> Query<T>(string sql, params object[] args)
        {
            return CommonMap.MapToIEnumerable<T>(ExecuteDataTable(sql, args));
            //using (var _Connection = DbService())
            //{
            //    CommandType commType = new CommandType();
            //    using (var cmd = CreateCommand(null, _Connection, commType, sql, args))
            //    {
            //        //IDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            //        using (IDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            //        {
            //            OnExecutedCommand(cmd);
            //            List<string> field = new List<string>(dr.FieldCount);
            //            for (int i = 0; i < dr.FieldCount; i++)
            //            {
            //                field.Add(dr.GetName(i).ToLower());
            //            }
            //            while (dr.Read())
            //            {
            //                T model = Activator.CreateInstance<T>();
            //                foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
            //                {
            //                    if (field.Contains(property.Name.ToLower()))
            //                    {
            //                        if (property.PropertyType.IsEnum) //属性类型是否表示枚举
            //                        {
            //                            object enumName = Enum.ToObject(property.PropertyType, property.GetValue(model, null));
            //                            property.SetValue(model, enumName, null); //获取枚举值，设置属性值
            //                        }
            //                        else
            //                        {
            //                            if (!CommonMap.IsNullOrDBNull(dr[property.Name]))
            //                            {
            //                                property.SetValue(model, CommonMap.HackType(dr[property.Name], property.PropertyType), null);
            //                            }
            //                        }
            //                    }
            //                }
            //                yield return model;
            //            }
            //        }
            //    }
            //}
        }

        /// <summary>
        /// 泛型方法，执行查询，返回单个泛型T
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="args">参数</param>
        /// <returns>返回查询结果T</returns>
        /// <example><code>
        /// Model.TB_HOSPITALPROCURECATALOG model1 = db.QueryModel<Model.TB_HOSPITALPROCURECATALOG>("select * from tb_hospitalprocurecatalog where orgid='Y292604' and procurecatalogid='6155'");
        /// </code></example>
        public T QueryModel<T>(string sql, params object[] args)
        {
            DataTable dt = ExecuteDataTable(sql, args);
            if (dt.Rows.Count > 0)
                return CommonMap.MapToModel<T>(dt.Rows[0]);
            else
                return Activator.CreateInstance<T>();
            //using (var _Connection = DbService())
            //{
            //    CommandType commType = new CommandType();
            //    using (var cmd = CreateCommand(null, _Connection, commType, sql, args))
            //    {
            //        using (IDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            //        {
            //            OnExecutedCommand(cmd);
            //            if (dr.Read())
            //            {
            //                List<string> list = new List<string>(dr.FieldCount);
            //                for (int i = 0; i < dr.FieldCount; i++)
            //                {
            //                    list.Add(dr.GetName(i).ToLower());
            //                }
            //                T model = Activator.CreateInstance<T>();
            //                foreach (PropertyInfo pi in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
            //                {
            //                    if (list.Contains(pi.Name.ToLower()))
            //                    {
            //                        if (pi.PropertyType.IsEnum) //属性类型是否表示枚举
            //                        {
            //                            object enumName = Enum.ToObject(pi.PropertyType, pi.GetValue(model, null));
            //                            pi.SetValue(model, enumName, null); //获取枚举值，设置属性值
            //                        }
            //                        else
            //                        {
            //                            if (!CommonMap.IsNullOrDBNull(dr[pi.Name]))
            //                            {
            //                                pi.SetValue(model, CommonMap.HackType(dr[pi.Name], pi.PropertyType), null);
            //                            }
            //                        }
            //                    }
            //                }
            //                return model;
            //            }
            //        }
            //        return default(T);
            //    }
            //}
        }


        /// <summary>
        /// 返回单个字段object的list
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public IEnumerable<T> QueryObjectList<T>(string sql, params object[] args)
        {
            using (var _Connection = DbService())
            {
                CommandType commType = new CommandType();
                using (var cmd = CreateCommand(null, _Connection, commType, sql, args))
                {
                    IDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    OnExecutedCommand(cmd);
                    List<string> field = new List<string>(dr.FieldCount);
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        field.Add(dr.GetName(i).ToLower());
                    }
                    while (dr.Read())
                    {
                        yield return CommonMap.ChangeType<T>(dr[0]);
                    }
                }
            }
        }
        /// <summary>
        /// 返回keyvaluepair的list
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public Dictionary<object, object> QueryObjectList(string sql, params object[] args)
        {
            Dictionary<object, object> diclist = new Dictionary<object, object>();
            using (var _Connection = DbService())
            {
                CommandType commType = new CommandType();
                using (var cmd = CreateCommand(null, _Connection, commType, sql, args))
                {
                    IDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    OnExecutedCommand(cmd);
                    List<string> field = new List<string>(dr.FieldCount);
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        field.Add(dr.GetName(i).ToLower());
                    }
                    while (dr.Read())
                    {
                        diclist.Add(dr[0], dr[1]);
                    }
                }
            }
            return diclist;
        }
        #endregion

        #region 构造分页
        /// <summary>
        /// 泛型方法，执行分页查询并返回<see cref="ListPage<T>"/>结果
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <param name="sql">执行的SQL语句</param>
        /// <param name="itemsPerPage">每页条目数</param>
        /// <param name="currentPage">第几页</param>
        /// <param name="args">参数</param>
        /// <returns>返回<see cref="ListPage<T>"/>结果</returns>
        public ListPage<T> GetListPage<T>(string sql, int itemsPerPage = 20, int currentPage = 1, params object[] args)
        {
            ListPage<T> page = new ListPage<T>() { CurrentPage = currentPage, ItemsPerPage = itemsPerPage };
            BuilderPageSql(sql, itemsPerPage, currentPage);
            if (itemsPerPage == 0)
            {
                page.Items = Query<T>(_sqlPage, args).ToList<T>();
                page.TotalItems = page.Items.Count;
            }
            else
            {
                int itemcount = ExecuteScalar<int>(_sqlCount, args);
                page.TotalItems = itemcount;
                if (itemcount == 0)
                    page.Items = new List<T>();
                else
                    page.Items = Query<T>(_sqlPage, args).ToList<T>();
            }
            return page;
        }

        /// <summary>
        /// 执行分页查询并返回<see cref="TablePage"/>结果
        /// </summary>
        /// <param name="sql">执行的SQL语句</param>
        /// <param name="itemsPerPage">每页条目数</param>
        /// <param name="currentPage">第几页</param>
        /// <param name="args">参数</param>
        /// <returns>返回<see cref="TablePage"/>结果</returns>
        public TablePage GetTablePage(string sql, int itemsPerPage = 20, int currentPage = 1, params object[] args)
        {
            TablePage page = new TablePage() { CurrentPage = currentPage, ItemsPerPage = itemsPerPage };
            BuilderPageSql(sql, itemsPerPage, currentPage);
            if (itemsPerPage == 0)
            {
                page.Items = ExecuteDataTable(_sqlPage, args);
                page.TotalItems = page.Items.Rows.Count;
            }
            else
            {
                int itemcount = ExecuteScalar<int>(_sqlCount, args);
                page.TotalItems = itemcount;
                if (itemcount == 0)
                    page.Items = new DataTable();
                else
                    page.Items = ExecuteDataTable(_sqlPage, args);
            }
            return page;
        }

        static Regex rxColumns = new Regex(@"\A\s*SELECT\s+((?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|.)*?)(?<!,\s+)\bFROM\b", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
        static Regex rxOrderBy = new Regex(@"\bORDER\s+BY\s+(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:ASC|DESC))?(?:\s*,\s*(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:ASC|DESC))?)*", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
        static Regex rxDistinct = new Regex(@"\ADISTINCT\s", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
        static Regex rxGroupBy = new Regex(@"\bGROUP\s+BY\s", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

        private string _sqlPage;
        private string _sqlCount;

        /// <summary>
        /// 创建用于分页查询的sql语句
        /// </summary>
        private void BuilderPageSql(string _sql, int _pageSize, int _currentPage)
        {
            string sqlSelectRemoved, sqlOrderBy;
            if (!SplitSqlForPaging(_sql, out sqlSelectRemoved, out sqlOrderBy))
                throw new Exception("分页查询时不能解析用于查询的SQL语句");
            sqlSelectRemoved = rxOrderBy.Replace(sqlSelectRemoved, "");
            if (_pageSize != 0)
            {
                if (rxDistinct.IsMatch(sqlSelectRemoved))
                {
                    sqlSelectRemoved = "Trade_inner.* FROM (SELECT " + sqlSelectRemoved + ") Trade_inner";
                }
                if (_dbType == DataBaseType.MySql)
                {
                    _sqlPage = string.Format("select {0} {1} limit {2},{3}", sqlSelectRemoved, sqlOrderBy, (_currentPage - 1) * _pageSize,  _pageSize);
                }
                else
                {
                    _sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) rn, {1}) Trade_paged WHERE rn> {2} AND rn<= {3}", sqlOrderBy == null ? "ORDER BY (SELECT NULL)" : sqlOrderBy, sqlSelectRemoved, (_currentPage - 1) * _pageSize, _currentPage * _pageSize);
                }
            }
            else
            {
                _sqlPage = string.Format("SELECT {0} {1}", sqlSelectRemoved, sqlOrderBy);
            }

        }

        /// <summary>
        /// 为分页查询对sql语句进行解析分割
        /// </summary>
        /// <param name="sqlSelectRemoved"></param>
        /// <param name="sqlOrderBy"></param>
        /// <returns></returns>
        private bool SplitSqlForPaging(string _sql, out string sqlSelectRemoved, out string sqlOrderBy)
        {
            sqlSelectRemoved = null;
            sqlOrderBy = null;

            var m = rxOrderBy.Match(_sql);
            Group g;
            if (!m.Success)
            {
                sqlOrderBy = null;
            }
            else
            {
                g = m.Groups[0];
                sqlOrderBy = g.ToString();
                _sql = _sql.Substring(0, g.Index) + _sql.Substring(g.Index + g.Length);
            }

            m = rxColumns.Match(_sql);
            if (!m.Success)
                return false;

            g = m.Groups[1];
            sqlSelectRemoved = _sql.Substring(g.Index);

            if (rxDistinct.IsMatch(sqlSelectRemoved))
            {
                _sqlCount = string.Format("select count(*) from ({0} {1} {2})A", _sql.Substring(0, g.Index), m.Groups[1].ToString().Trim(), _sql.Substring(g.Index + g.Length));
            }
            else if (rxGroupBy.IsMatch(sqlSelectRemoved))
            {
                _sqlCount = string.Format("select count(*) from ({0} {1} {2})A", _sql.Substring(0, g.Index), m.Groups[1].ToString().Trim(), _sql.Substring(g.Index + g.Length));

            }
            else
            {
                _sqlCount = _sql.Substring(0, g.Index) + "COUNT(*) " + _sql.Substring(g.Index + g.Length);
            }
            return true;
        }

        #endregion

        #region 单表DML操作
        /// <summary>
        /// 对单表进行Insert操作
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <param name="model">实体参数</param>
        /// <param name="tran">事务</param>
        /// <returns>返回影响的行数</returns>
        public int Insert<T>(T model, Transaction tran = null) where T : class
        {
            string sql = string.Empty;
            string tableName = model.GetType().Name;

            object[] customAttribute = null;
            object[] tableAttribute = null;

            Type t = model.GetType();
            tableAttribute = t.GetCustomAttributes(typeof(TableAttribute), false);
            if (tableAttribute != null && tableAttribute.Length > 0)
                tableName = (tableAttribute[0] as TableAttribute).TableName;

            sql = "insert into " + tableName;
            StringBuilder fieldList = new StringBuilder();
            StringBuilder valueList = new StringBuilder();
            PropertyInfo[] pis = t.GetProperties();
            foreach (PropertyInfo pi in pis)
            {
                if (pi.GetValue(model, null) == null)//如果属性值为null 不插入
                {
                    continue;
                }
                customAttribute = pi.GetCustomAttributes(typeof(ColumnAttribute), false);
                if (customAttribute.Count() == 0 || (customAttribute.Count() == 1 && !(customAttribute[0] as ColumnAttribute).Identity))
                {
                    fieldList.AppendFormat(",{0}", pi.Name);
                    valueList.AppendFormat(",@{0}", pi.Name);
                }
            }
            if (fieldList.Length == 0 || valueList.Length == 0)
                throw new ArgumentException();

            sql = string.Format("insert into {0}({1}) values({2})", tableName, fieldList.Remove(0, 1), valueList.Remove(0, 1));
            if (tran != null)
                return ExecuteNonQuery(tran, sql, model);
            else
                return ExecuteNonQuery(sql, model);
        }

        /// <summary>
        /// 对单表进行Update操作
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <param name="model">实体参数</param>
        /// <param name="tran">事务</param>
        /// <returns>返回影响的行数</returns>
        public int Update<T>(T model, Transaction tran = null) where T : class
        {
            string sql = string.Empty;
            string tableName = model.GetType().Name;
            Type t = model.GetType();
            object[] customAttribute = null;
            object[] tableAttribute = null;

            tableAttribute = t.GetCustomAttributes(typeof(TableAttribute), false);
            if (tableAttribute != null && tableAttribute.Length > 0)
                tableName = (tableAttribute[0] as TableAttribute).TableName;

            StringBuilder keyValuePaire = new StringBuilder();
            StringBuilder condition = new StringBuilder();
            PropertyInfo[] pis = t.GetProperties();
            foreach (PropertyInfo pi in pis)
            {
                if (pi.GetValue(model, null) == null)//如果属性值为null 不更新
                {
                    continue;
                }
                customAttribute = pi.GetCustomAttributes(typeof(ColumnAttribute), false);
                if (customAttribute.Count() == 1 && (customAttribute[0] as ColumnAttribute).PrimaryKey)
                    condition.AppendFormat(" and {0}=@{0}", pi.Name);
                else
                    keyValuePaire.AppendFormat(",{0}=@{0}", pi.Name);
            }
            if (condition.Length == 0 || keyValuePaire.Length == 0)
                throw new ArgumentException();

            sql = string.Format("update {0} set {1} where 1=1 {2}", tableName, keyValuePaire.Remove(0, 1), condition);

            if (tran != null)
                return ExecuteNonQuery(tran, sql, model);
            else
                return ExecuteNonQuery(sql, model);
        }

        /// <summary>
        /// 对单表进行Delete操作
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <param name="model">实体参数</param>
        /// <param name="tran">事务</param>
        /// <returns>返回影响的行数</returns>
        public int Delete<T>(T model, Transaction tran = null) where T : class
        {
            string sql = string.Empty;
            string tableName = model.GetType().Name;
            Type t = model.GetType();
            object[] tableAttribute = null;

            tableAttribute = t.GetCustomAttributes(typeof(TableAttribute), false);
            if (tableAttribute != null && tableAttribute.Length > 0)
                tableName = (tableAttribute[0] as TableAttribute).TableName;

            StringBuilder condition = new StringBuilder();
            PropertyInfo[] pis = t.GetProperties();
            foreach (PropertyInfo pi in pis)
            {
                if (pi.GetValue(model, null) == null)//如果属性值为null 不更新
                {
                    continue;
                }
                condition.AppendFormat(" and {0}=@{0}", pi.Name);
            }
            if (condition.Length == 0)
                throw new ArgumentException();

            sql = string.Format("delete from {0} where 1=1 {1}", tableName, condition);

            if (tran != null)
                return ExecuteNonQuery(tran, sql, model);
            else
                return ExecuteNonQuery(sql, model);
        }

        /// <summary>
        /// 单表查询
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <param name="model">参数</param>
        /// <returns>返回查询结果实体T</returns>
        public T Query<T>(T model) where T : class
        {
            string sql = string.Empty;
            string tableName = model.GetType().Name;
            Type t = model.GetType();
            object[] tableAttribute = null;

            tableAttribute = t.GetCustomAttributes(typeof(TableAttribute), false);
            if (tableAttribute != null && tableAttribute.Length > 0)
                tableName = (tableAttribute[0] as TableAttribute).TableName;

            StringBuilder condition = new StringBuilder();
            PropertyInfo[] pis = t.GetProperties();
            foreach (PropertyInfo pi in pis)
            {
                if (pi.GetValue(model, null) == null)//如果属性值为null 不更新
                {
                    continue;
                }
                condition.AppendFormat(" and {0}=@{0}", pi.Name);
            }
            if (condition.Length == 0)
                throw new ArgumentException();

            sql = string.Format("select * from {0} where 1=1 {1}", tableName, condition);

            return QueryModel<T>(sql, model);
        }
        #endregion

        /// <summary>
        /// 根据对象获取查询条件，对象可以为实体或匿名对象
        /// </summary>
        /// <param name="db"></param>
        /// <param name="query"></param>
        /// <param name="createorder"></param>
        /// <returns></returns>
        public string GetWhereCondition(object query, bool createorder = true)
        {
            if (query == null)
                return string.Empty;
            StringBuilder where = new StringBuilder();
            StringBuilder keyWhere = new StringBuilder();
            string sortfields = string.Empty;
            string append = string.Empty;
            foreach (PropertyInfo pi in query.GetType().GetProperties())
            {
                if (pi.GetValue(query, null) == null || string.IsNullOrEmpty(pi.GetValue(query, null).ToString().Trim()))
                    continue;
                //如果是追加的查询条件
                if (pi.Name.ToLower() == "append")
                {
                    append = pi.GetValue(query, null).ToString();
                }
                else if (pi.Name.ToLower() == "sortfields")
                {
                    sortfields = pi.GetValue(query, null).ToString();
                }
                else
                {
                    if (pi.Name.ToLower().EndsWith("_key"))
                        keyWhere.AppendFormat(" and {0} =@{1}", pi.Name.Substring(0, pi.Name.Length - 4), pi.Name);
                    else if (pi.Name.ToLower().Contains("areaid"))
                    {
                        if (pi.GetValue(query, null).ToString().EndsWith("0000"))
                            keyWhere.AppendFormat(" and {0} like '{1}%'", pi.Name, pi.GetValue(query, null).ToString().Substring(0, 2));
                        else if (pi.GetValue(query, null).ToString().EndsWith("00"))
                            keyWhere.AppendFormat(" and {0} like '{1}%'", pi.Name, pi.GetValue(query, null).ToString().Substring(0, 4));
                        else
                            keyWhere.AppendFormat(" and {0} =@{1}", pi.Name, pi.Name);
                    }
                    //else if (pi.Name.ToLower().Contains("id"))
                    //    keyWhere.AppendFormat(" and {0} =@{1}", pi.Name, pi.Name);
                    else if (pi.Name.ToLower().EndsWith("_to"))
                        where.AppendFormat(" and {0} <=@{1}", pi.Name.Substring(0, pi.Name.Length - 3), pi.Name);
                    else if (pi.Name.ToLower().EndsWith("_from"))
                        where.AppendFormat(" and {0} >=@{1}", pi.Name.Substring(0, pi.Name.Length - 5), pi.Name);
                    else
                    {
                        if (pi.PropertyType.IsValueType || ((pi.PropertyType.IsGenericType && pi.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))))
                            where.AppendFormat(" and {0} =@{0}", pi.Name);
                        else
                        {
                            if (_dbType == DataBaseType.MySql)
                                where.AppendFormat(" and {0} like concat('%',@{0},'%')", pi.Name);
                            else
                                where.AppendFormat(" and {0} like '%'+@{0}+'%'", pi.Name);
                        }
                    }
                }
            }
            if (!string.IsNullOrEmpty(append.Trim()))
                where.AppendFormat(" and {0}", append);
            if (!string.IsNullOrEmpty(sortfields) && createorder)
                where.AppendFormat(" order by {0}", sortfields);
            if (keyWhere.Length > 0)
                where.Insert(0, keyWhere.ToString());
            return where.ToString();
        }

        /// <summary>
        /// 根据对象获取最原始的查询条件，对象可以为实体或匿名对象---一般情况下不用
        /// </summary>
        /// <param name="db"></param>
        /// <param name="query"></param>
        /// <param name="createorder"></param>
        /// <returns></returns>
        public string GetWhereCondition_Direct(object query, bool createorder = true)
        {
            StringBuilder where = new StringBuilder();
            StringBuilder keyWhere = new StringBuilder();
            string sortfields = string.Empty;
            string append = string.Empty;
            foreach (PropertyInfo pi in query.GetType().GetProperties())
            {
                if (pi.GetValue(query, null) == null || string.IsNullOrEmpty(pi.GetValue(query, null).ToString()))
                    continue;
                //如果是追加的查询条件
                if (pi.Name.ToLower() == "append")
                {
                    append = pi.GetValue(query, null).ToString();
                }
                else if (pi.Name.ToLower() == "sortfields")
                {
                    sortfields = pi.GetValue(query, null).ToString();
                }
                else
                {
                    if (pi.Name.ToLower().EndsWith("_key"))
                        keyWhere.AppendFormat(" and {0} = '{1}' ", pi.Name.Substring(0, pi.Name.Length - 4), pi.GetValue(query, null).ToString());
                    else if (pi.Name.ToLower().EndsWith("_to"))
                        where.AppendFormat(" and {0} <= '{1}'", pi.Name.Substring(0, pi.Name.Length - 3), pi.GetValue(query, null).ToString());
                    else if (pi.Name.ToLower().EndsWith("_from"))
                        where.AppendFormat(" and {0} >='{1}'", pi.Name.Substring(0, pi.Name.Length - 5), pi.GetValue(query, null).ToString());
                    else
                    {
                        if (pi.PropertyType.IsValueType || ((pi.PropertyType.IsGenericType && pi.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))))
                            where.AppendFormat(" and {0} ='{1}'", pi.Name, pi.GetValue(query, null).ToString());
                        else
                        {
                            //where.AppendFormat(" and {0} like '%{1}%'", pi.Name, pi.GetValue(query, null));
                            if (_dbType == DataBaseType.MySql)
                                where.AppendFormat(" and {0} like concat('%','{1}','%')", pi.Name, pi.GetValue(query, null).ToString());
                            else
                                where.AppendFormat(" and {0} like '%'+'{1}'+'%'", pi.Name, pi.GetValue(query, null).ToString());
                        }
                    }
                }
            }
            if (!string.IsNullOrEmpty(append))
                where.AppendFormat(" and {0}", append);
            if (!string.IsNullOrEmpty(sortfields) && createorder)
                where.AppendFormat(" order by {0}", sortfields);
            if (keyWhere.Length > 0)
                where.Insert(0, keyWhere.ToString());
            return where.ToString();
        }


    }

    /// <summary>
    /// 
    /// </summary>
    public class AnsiString
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="str"></param>
        public AnsiString(string str)
        {
            Value = str;
        }
        /// <summary>
        /// 
        /// </summary>
        public string Value { get; private set; }
    }
}
